﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.IO;
using System.Data;
using NPOI.XSSF.UserModel;
using System.Reflection;
using Microsoft.Win32;

namespace WPFNpoi
{
    /// <summary>
    /// Excel操作相关方法
    /// 创建者：唐开文
    /// 时间：2014年3月17日 17:53:57
    /// </summary>
    public class ExcelCommon
    {
        /// <summary>读取excel
        /// 默认第一行为标头
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <returns></returns>
        public static DataTable ImportExcel2007toDt(string strFileName, int colnums)
        {
            DataTable dt = new DataTable();
            IWorkbook hssfworkbook;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new XSSFWorkbook(file);
            }
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            //  dt = ImportExcel2007InDt(sheet, 0, false, colnums);
            return dt;
        }

        #region 导入
        /// <summary>
        /// 获取表头
        /// 如果不存在正确的表头那么就从A开始往后排序
        /// </summary>
        /// <param name="path"></param>
        /// <param name="sheetNum"></param>
        /// <param name="isMatch"></param>
        /// <returns></returns>
        public static List<string> GetHeader(string path, int sheetNum)
        {
            List<string> headerlist = null;
            IWorkbook workbook;
            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                workbook = new XSSFWorkbook(file);
            }
            ISheet sheet = workbook.GetSheetAt(sheetNum);
            if (sheet.LastRowNum <= 0) return headerlist; //没有数据
            //获取表头
            var rows = sheet.GetRowEnumerator();
            rows.MoveNext();//下一集合
            IRow row = (XSSFRow)rows.Current;
            if (row.Cells != null && row.Cells.Count > 0)
            {
                headerlist = new List<string>();
                foreach (ICell cell in row.Cells)
                {
                    if (cell != null)
                    {
                        if (cell.CellType == CellType.String)
                            headerlist.Add(cell.StringCellValue);
                    }
                }
            }
            if (headerlist.Count != row.Cells.Count || headerlist == null || headerlist.Count == 0)
            {

                if (row.Cells != null && row.Cells.Count > 0)
                {
                    headerlist = new List<string>();
                    for (int i = 0; i < row.Cells.Count; i++)
                    {
                        string code = Convert.ToChar(((int)'A') + i).ToString();
                        headerlist.Add(code);
                    }
                }
            }
            return headerlist;
        }
        internal static DataTable ImportExcel(string path, int sheetNum, HeaderProperty[] headerProperty)
        {
            DataTable dt = new DataTable(Path.GetFileNameWithoutExtension(path) + "_Sheet" + sheetNum);
            IWorkbook workbook;
            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                workbook = new XSSFWorkbook(file);
            }
            ISheet sheet = workbook.GetSheetAt(sheetNum);
            var rows = sheet.GetRowEnumerator();
            bool b = false;//是否合法表头
            #region Datatable设置表头

            rows.MoveNext();//下一集合
            IRow row = (XSSFRow)rows.Current;
            if (row.Cells != null && row.Cells.Count > 0)
            {
                foreach (ICell cell in row.Cells)
                {
                    if (cell != null)
                    {
                        if (cell.CellType == CellType.String)
                        {
                            foreach (HeaderProperty item in headerProperty)
                            {
                                if (item.Name == cell.StringCellValue)
                                {
                                    dt.Columns.Add(item.Code, typeof(string));
                                    b = true;
                                }
                            }
                        }
                    }
                }
            }
            if (!b)
            {
                foreach (HeaderProperty item in headerProperty)
                {
                    dt.Columns.Add(item.Code, typeof(string));
                }
            }
            #endregion

            #region 数据
            if (b)
            {
                while (rows.MoveNext())
                {
                    row = (XSSFRow)rows.Current;
                    DataRow dataRow = dt.NewRow();
                    for (int i = 0; i < headerProperty.Length; i++)
                    {
                        ICell cell = row.GetCell(i);
                        if (cell == null) continue;
                        if (cell.CellType == CellType.Numeric)
                            dataRow[i] = cell.NumericCellValue;
                        if (cell.CellType == CellType.String)
                            dataRow[i] = cell.StringCellValue;
                    }
                    dt.Rows.Add(dataRow);
                }
            }
            else
            {
                for (int i = 0; i <= sheet.LastRowNum; i++)
                {
                    DataRow dataRow = dt.NewRow();
                    for (int j = 0; j < headerProperty.Length; j++)
                    {
                        ICell cell = sheet.GetRow(i).GetCell(j);
                        if (cell == null) continue;
                        if (cell.CellType == CellType.Numeric)
                            dataRow[j] = cell.NumericCellValue;
                        if (cell.CellType == CellType.String)
                            dataRow[j] = cell.StringCellValue;

                    }
                    dt.Rows.Add(dataRow);
                }
            }
            #endregion

            return dt;
        }

        //导入
        public static DataTable ImportExcel(string path, int sheetNum)
        {
            DataTable dt = new DataTable(Path.GetFileNameWithoutExtension(path) + "_Sheet" + sheetNum);
            IWorkbook workbook;
            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                workbook = new XSSFWorkbook(file);
            }
            ISheet sheet = workbook.GetSheetAt(sheetNum);

            //获取表头
            var rows = sheet.GetRowEnumerator();
            rows.MoveNext();//下一集合
            IRow row = (XSSFRow)rows.Current;
            return dt;
        }
        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="path">Excel地址</param>
        /// <param name="sheetNum">第几章表</param>
        /// <param name="header">是否属于列在Excel中的第一行</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string path, int sheetNum, bool header)
        {
            DataTable dt = new DataTable(Path.GetFileNameWithoutExtension(path) + "_Sheet" + sheetNum);
            IWorkbook workbook;
            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                workbook = new XSSFWorkbook(file);
            }
            ISheet sheet = workbook.GetSheetAt(sheetNum);
            var rows = sheet.GetRowEnumerator();

            rows.MoveNext();
            IRow row = (XSSFRow)rows.Current;

            for (int i = 0; i < row.LastCellNum; i++)
            {
                ICell cell = row.GetCell(i);
                if (cell != null)
                {
                    string columnName = header ? cell.StringCellValue : i.ToString();
                    dt.Columns.Add(columnName, typeof(string));
                }

            }
            if (!header)
            {
                DataRow first = dt.NewRow();
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    ICell cell = row.GetCell(i);
                    first[i] = cell.StringCellValue;
                }
                dt.Rows.Add(first);
            }

            while (rows.MoveNext())
            {
                row = (XSSFRow)rows.Current;
                DataRow dataRow = dt.NewRow();

                for (int i = 0; i < row.LastCellNum; i++)
                {
                    ICell cell = row.GetCell(i);
                    if (cell == null) continue;
                    if (cell.CellType == CellType.Numeric)
                        dataRow[i] = cell.NumericCellValue;
                    if (cell.CellType == CellType.String)
                        dataRow[i] = cell.StringCellValue;
                }
                dt.Rows.Add(dataRow);
            }

            return dt;
        }

        #endregion
        #region 导出
        /// <summary>
        /// 导出数据【根据地址模版创建新的工作薄】
        /// </summary>
        /// <param name="path">模版地址</param>
        /// <param name="promiselarry">约定值结合</param>
        /// <param name="headerProperty">列标题集合</param>
        /// <param name="oaarry">数据</param>
        public static void ExportExcel(string path, Promise[] promiselarry, HeaderProperty[] headerProperty, object[] oaarry)
        {
            try
            {
                XSSFWorkbook workbook = ExcelCommon.GetXSSFWorkBook(path);//获取模版 工作薄
                ISheet sheet = workbook.GetSheetAt(0);//模版表 默认第一张 
                HeaderColCoordinate[] headcollidt = GetHeaderColCoordinate(sheet, promiselarry);//获取 标题头的列坐标 

                #region 根据约定数据、新增数据数量 坐标 新增行 和位移行
                //获取最大列头的 列坐标
                List<HeaderColCoordinate> descHeadcollist = headcollidt.ToList().OrderByDescending(o => o.ColIndx).ToList();// as List<HeaderColCoordinate>;
                int maxColIndex = descHeadcollist[0].ColIndx;
                ShiftCreateRows(sheet, oaarry.Length, maxColIndex, promiselarry);
                #endregion

                #region 数据
                DataToSheet(sheet, headcollidt, oaarry);
                #endregion

                //约定
                #region 替换约定
                ExcelCommon.SetReplaceValue(sheet, promiselarry);
                #endregion

                #region 替换标题头
                ExcelCommon.SetReplaceHeader(sheet, headerProperty);
                #endregion

                SaveFileDialog sfd = new SaveFileDialog();
                sfd.Filter = "Excel(2007)|*.xlsx|Excel(97-03)|*.xls";
                bool? b = sfd.ShowDialog();
                if (b.Value == true)
                {
                    FileStream stm = new FileStream(sfd.FileName, FileMode.Create, FileAccess.Write);
                    workbook.Write(stm);
                    stm.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 使用数据流导出Excel数据
        /// </summary>
        /// <param name="stream">数据流</param>
        /// <param name="promiselarry">约定值结合</param>
        /// <param name="headerProperty">列标题集合</param>
        /// <param name="oaarry">数据</param>
        public static void ExportExcel(Stream stream, Promise[] promiselarry, HeaderProperty[] headerProperty, object[] oaarry)
        {
            try
            {
                XSSFWorkbook workbook = new XSSFWorkbook(stream);//获取模版 工作薄
                ISheet sheet = workbook.GetSheetAt(0);//模版表 默认第一张 
                HeaderColCoordinate[] headcollidt = GetHeaderColCoordinate(sheet, promiselarry);//获取 标题头的列坐标 

                #region 根据约定数据、新增数据数量 坐标 新增行 和位移行
                //获取最大列头的 列坐标
                List<HeaderColCoordinate> descHeadcollist = headcollidt.ToList().OrderByDescending(o => o.ColIndx).ToList();// as List<HeaderColCoordinate>;
                int maxColIndex = descHeadcollist[0].ColIndx;
                ShiftCreateRows(sheet, oaarry.Length, maxColIndex, promiselarry);
                #endregion

                #region 数据
                DataToSheet(sheet, headcollidt, oaarry);
                #endregion

                //约定
                #region 替换约定
                ExcelCommon.SetReplaceValue(sheet, promiselarry);
                #endregion

                #region 替换标题头
                ExcelCommon.SetReplaceHeader(sheet, headerProperty);
                #endregion

                SaveFileDialog sfd = new SaveFileDialog();
                sfd.Filter = "Excel(2007)|*.xlsx|Excel(97-03)|*.xls";
                bool? b = sfd.ShowDialog();
                if (b.Value == true)
                {
                    FileStream stm = new FileStream(sfd.FileName, FileMode.Create, FileAccess.Write);
                    workbook.Write(stm);
                    stm.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


        /// <summary>
        /// 位移并新增行
        /// </summary>
        /// <param name="sheet">表</param>
        /// <param name="dataNum">导入的数据数量</param>
        /// <param name="maxColIndex">表头的最大列坐标</param>
        /// <param name="promiselarry">约定值</param>
        private static void ShiftCreateRows(ISheet sheet, int dataNum, int maxColIndex, Promise[] promiselarry)
        {
            List<int> rowlist = new List<int>();
            foreach (Promise promise in promiselarry)
            {
                int[] coordinate = GetRowColCoordinate(sheet, promise.PromiseValue);
                if (coordinate != null)
                {
                    if (coordinate[1] <= maxColIndex) //列坐标小于 最大列
                    {
                        if (true)
                        {

                        }
                        rowlist.Add(coordinate[0]);//获取行
                    }
                }
            }
            //样式

            ICellStyle style = null;
            if (sheet.LastRowNum > 1)
                style = sheet.GetRow(1).GetCell(0).CellStyle;
            //位移
            rowlist = rowlist.OrderBy(o => o).ToList();

            int startRow = rowlist[0];
            int endRow = rowlist[rowlist.Count - 1];
            int n = endRow - startRow;
            ShiftRows(sheet, startRow, endRow, n);
            //创建新的空行列 
            for (int i = startRow; i < endRow; i++)
            {
                IRow row = sheet.CreateRow(i);
                for (int j = 0; j <= maxColIndex; j++)
                {
                    ICell cell = row.CreateCell(j);
                    //cell.SetCellValue("TKW");
                    if (style != null)
                        cell.CellStyle = style;//复制样式
                }
            }
        }

        private static void DataToSheet(ISheet sheet, HeaderColCoordinate[] headcollidt, object[] oaarry)
        {

            #region 映射加数据
            int rowsindex = 1;//从1行开始加载数据
            foreach (var item in oaarry)
            {
                Type type = item.GetType();
                PropertyInfo[] props = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
                if (props != null)
                {
                    foreach (PropertyInfo pi in props)
                    {
                        try
                        {
                            foreach (HeaderColCoordinate headCol in headcollidt)
                            {
                                bool b = headCol.Code.Contains(pi.Name);
                                if (b)
                                {
                                    var var = pi.GetValue(item, null);
                                    //开始加数据
                                    // sheet .
                                    sheet.GetRow(rowsindex).GetCell(headCol.ColIndx).SetCellValue(var.ToString());

                                }
                            }
                        }
                        catch { }
                    }
                }
                rowsindex++;//

            }

            #endregion


        }
        /// <summary>
        /// 获取标题头的坐标
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="promiselarry">需要排除的约定集合</param>
        /// <returns></returns>
        public static HeaderColCoordinate[] GetHeaderColCoordinate(ISheet sheet, Promise[] promiselarry)
        {
            #region 获取 标题头的列坐标
            List<HeaderColCoordinate> headcollidt = new List<HeaderColCoordinate>();
            IRow row = sheet.GetRow(0);
            for (int i = 0; i < row.LastCellNum; i++)
            {
                ICell cell = row.GetCell(i);
                if (cell != null)
                {
                    if (cell.CellType == CellType.String)
                    {
                        string code = row.GetCell(i).StringCellValue;
                        bool b = true;
                        foreach (Promise item in promiselarry)
                        {
                            if (code.Contains(item.PromiseValue))
                            {
                                b = false;
                            }
                        }
                        if (b)
                            headcollidt.Add(new HeaderColCoordinate() { Code = code, ColIndx = i });
                    }
                }
            }
            return headcollidt.ToArray();
            #endregion
        }
        #endregion


        #region 相关操作：新增、修改、删除、位移
        /// <summary>
        /// 位移表
        /// </summary>
        /// <param name="sheet">表</param>
        /// <param name="startRow">该行开始转移 </param>
        /// <param name="endRow">结束移动的行 </param>
        /// <param name="n">行移位数</param>
        public static void ShiftRows(ISheet sheet, int startRow, int endRow, int n)
        {
            sheet.ShiftRows(startRow, endRow, n);
        }
        /// <summary>
        /// 修改 指定列
        /// </summary>
        /// <param name="sheet">需要修改的表</param>
        /// <param name="rowcol">行列坐标</param>
        /// <param name="value">值</param>
        public static void SetCellValue(ISheet sheet, int[] rowcol, string value)
        {
            try
            {
                sheet.GetRow(rowcol[0]).GetCell(rowcol[1]).SetCellValue(value);
            }
            catch (Exception ex)
            {
                throw new Exception("修改失败！\n" + ex.Message);
            }
        }
        /// <summary>
        ///插入行
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="rows"></param>
        /// <param name="value"></param>
        public static void InsertRow(ISheet sheet, int rows, string[] value)
        {
            IRow row = sheet.CreateRow(rows);
            for (int i = 0; i < value.Length; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(value[i]);
            }

        }

        public static void InsertRow(ISheet sheet, int rows, Object[] oA)
        {
            if (oA != null || oA.Length > 0)
            {
                foreach (Object item in oA)
                {
                    Type type = item.GetType();
                    PropertyInfo[] props = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
                    if (props != null)
                    {
                        int colindex = 0;
                        IRow row = sheet.CreateRow(rows);
                        foreach (PropertyInfo pi in props)
                        {
                            try
                            {
                                var oj = pi.GetValue(item, null);
                                ICell cell = row.CreateCell(colindex);
                                cell.SetCellValue(oj.ToString());
                                //  PropertyInfo prop = item.GetType().GetProperty(pi.Name);// typeof(object).GetProperty(pi.Name);
                            }
                            catch { }
                        }
                    }
                    rows++;
                }


            }
        }


        /// <summary>
        /// 根据匹配值获取 行列坐标
        /// </summary>
        /// <param name="sheet">表</param>
        /// <param name="matchValue">匹配值</param>
        /// <returns></returns>
        public static int[] GetRowColCoordinate(ISheet sheet, string matchValue)
        {
            int[] rowcol = null;
            for (int i = 0; i < sheet.LastRowNum; i++)
            {
                for (int j = 0; j < sheet.GetRow(i).LastCellNum; j++)
                {
                    XSSFCell cell = (XSSFCell)sheet.GetRow(i).GetCell(j);
                    if (cell != null)
                    {
                        if (cell.CellType == CellType.String)
                        {
                            if (cell.StringCellValue.Contains(matchValue))
                            {
                                rowcol = new int[2];
                                rowcol[0] = cell.RowIndex;
                                rowcol[1] = cell.ColumnIndex;
                                break;
                            }
                        }
                    }

                }
            }
            return rowcol;
        }
        /// <summary>
        /// 替换模版约定值
        /// </summary>
        /// <param name="sheet">表</param>
        /// <param name="promiselarry">约定集合</param>
        public static void SetReplaceValue(ISheet sheet, Promise[] promiselarry)
        {
            if (promiselarry == null || promiselarry.Length == 0) return;
            for (int i = 0; i <= sheet.LastRowNum; i++)
            {
                for (int j = 0; j < sheet.GetRow(i).LastCellNum; j++)
                {
                    XSSFCell cell = (XSSFCell)sheet.GetRow(i).GetCell(j);
                    if (cell != null)
                    {
                        if (cell.CellType == CellType.String)
                        {
                            foreach (Promise item in promiselarry)
                            {
                                if (cell.StringCellValue.Contains(item.PromiseValue))
                                {
                                    cell.SetCellValue(cell.StringCellValue.Replace(item.PromiseValue, item.Value));
                                }
                            }
                        }
                    }

                }
            }
        }

        /// <summary>
        /// 替换表头
        /// </summary>
        /// <param name="sheet">表</param>
        /// <param name="headerProperty">表头集合</param>
        internal static void SetReplaceHeader(ISheet sheet, HeaderProperty[] headerProperty)
        {
            if (headerProperty == null || headerProperty.Length == 0) return;
            if (sheet.LastRowNum == 0) return;
            IRow row = sheet.GetRow(0);
            if (row.LastCellNum == 0) return;
            for (int i = 0; i < row.LastCellNum; i++)
            {
                ICell cell = row.GetCell(i);
                if (cell != null)
                {
                    if (cell.CellType == CellType.String)
                    {
                        foreach (HeaderProperty item in headerProperty)
                        {
                            if (cell.StringCellValue.Contains(item.Code))
                            {
                                cell.SetCellValue(cell.StringCellValue.Replace(cell.StringCellValue, item.Name));
                            }
                        }
                    }
                }
            }


        }
        #endregion

        #region 2007版本的Excel XSSF

        #region 获取基础数据
        /// <summary>
        /// 获取xlsx的工作薄
        /// </summary>
        /// <param name="path">Excel文件地址</param>
        /// <returns></returns>
        public static XSSFWorkbook GetXSSFWorkBook(string path)
        {
            using (FileStream fs = File.OpenRead(path))   //打开myxls.xls文件
            {
                XSSFWorkbook wk = new XSSFWorkbook(fs);   //把xls文件中的数据写入wk中
                return wk;
            }
        }
        /// <summary>
        /// 获取指定表
        /// </summary>
        /// <param name="path">Excel文件地址</param>
        /// <param name="sheetNum">第几张表 从0开始 超出界限返回NULL</param>
        /// <returns></returns>
        public static ISheet GetSheet(string path, int sheetNum)
        {
            XSSFWorkbook workbook = GetXSSFWorkBook(path);
            if (workbook.NumberOfSheets <= sheetNum) return null;
            ISheet sheet = workbook.GetSheetAt(sheetNum);   //读取当前表数据
            return sheet;
        }
        /// <summary>
        /// 获取指定行数据
        /// </summary>
        /// <param name="path">Excel文件地址</param>
        /// <param name="sheetNum">第几张表 从0开始 超出界限返回NULL</param>
        /// <param name="rows">行数 从0开始 超出界限返回NULL</param>
        /// <returns></returns>
        public static IRow GetRowData(string path, int sheetNum, int rows)
        {
            ISheet sheet = GetSheet(path, sheetNum);
            if (sheet.LastRowNum >= rows) return null;
            IRow row = sheet.GetRow(rows);  //读取当前行数据
            return row;
        }
        /// <summary>
        /// 获取指定列数据
        /// </summary>
        /// <param name="path">Excel文件地址</param>
        /// <param name="sheetNum">第几张表 从0开始 超出界限返回NULL</param>
        /// <param name="rows">行数 从0开始 超出界限返回NULL</param>
        /// <param name="cols">指定列数 从0开始 超出界限返回NULL</param>
        /// <returns></returns>
        public static ICell GetRowCellData(string path, int sheetNum, int rows, int cols)
        {
            ICell cell = null;
            IRow row = GetRowData(path, sheetNum, rows);
            if (row == null) return null;
            cell = row.GetCell(cols);
            return cell;
        }
        #endregion

        #endregion


    }
}
